Comparison Operators in MySQL and Their Behavior with NULL
Comparison operators in MySQL are used to compare two values and return TRUE (1), FALSE (0), or NULL (unknown). When NULL values are involved, comparisons behave differently because NULL represents an unknown value.
=(equal to)!=or<>(not equal to)>(greater than)<(less than)>=(greater or equal)<=(less or equal)BETWEENIN/NOT INLIKE
Normally, comparison operators return TRUE or FALSE — except when NULL is involved.
Any comparison with NULL returns NULL (unknown).
This includes expressions like:
5 = NULL,NULL <> 10,NULL > 0.Even
NULL = NULLreturns NULL (because NULL means unknown, so two unknowns cannot be equal).
<=>is MySQL's NULL-safe comparison operator.Returns 1 if both values are equal — including when both are NULL.
Returns 0 otherwise.
INignores NULL values in the list.NOT INreturns NULL if the list contains a NULL — causing unexpected results.
NULL means unknown, so comparisons involving NULL result in NULL.
Use
<=>for NULL-safe comparisons.Beware of NULL inside
NOT IN— it can make the whole condition NULL.NULL does not behave like 0 or empty string.
In summary: MySQL's comparison operators work normally unless NULL is present. When NULL appears, the result becomes NULL unless you use the NULL-safe operator <=>.